package com.clkj.goldpartner.dao;

import com.clkj.goldpartner.pojo.Goods;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

/**
 * @author 杨阿旺
 * @date 2021-03-02 14:00
 */
@Repository
public interface GoodsMapper extends Mapper<Goods> {
    /**
     * 获取最后一位编号
     *
     * @return String
     */
    @Select("select top 1 goods_num from t_cl_goods group by goods_id,goods_num order by goods_id desc ")
    String getGoods_num();

    /**
     * 添加商品
     *
     * @param Goods
     */
    @Update("insert into t_cl_goods values(#{goods_name},#{goods_num},#{bar_code},#{goods_format},#{registration_NO},#{production_NO},#{product_factory}," +
            "#{curing_type},#{curing_time},#{shop_id},#{category_id},#{goods_status},#{goods_note},#{created_time},#{goods_unit},#{goods_warning},#{storage_mode},#{UDI})")
    @Options(useGeneratedKeys = true, keyProperty = "goods_id")
    void addGoods(Goods Goods);

    /**
     * 模糊查询商品信息
     *
     * @param names
     * @param shop_id
     * @return
     */
    @Select("select a.*,b.category_name from t_cl_goods a left join t_cl_category b on a.category_id = b.category_id where (a.goods_num like '%${names}%' or a.UDI like '%${UDI}%') and a.goods_status = '0' and a.shop_id = #{shop_id} ")
    List<Map> listGoods(String names, Integer shop_id,String UDI);

    /**
     * 商品列表
     *
     * @param goods_num
     * @param goods_name
     * @param goods_format
     * @param category_id
     * @return
     */
    @Select("<script>" +
            "select * from t_cl_goods a left join t_cl_category b on a.category_id " +
            "= b.category_id where a.shop_id = #{shop_id} and a.goods_num like '%${goods_num}%' and a.goods_format like '%${goods_format}%' and a.goods_name like '%${goods_name}%' and a.UDI like '%${UDI}%'" +
            "<if test=' 0!=category_id '> and a.category_id = #{category_id} </if> " +
            "</script>")
    List<Map> listGoodsSearch(Integer shop_id, String goods_num, String goods_name, String goods_format, Integer category_id,String UDI);

    /**
     * 删除商品
     *
     * @param ids
     */
    @Update("update t_cl_goods set goods_status = 1 where goods_id in (${ids})")
    void updateStatus(@Param("ids") String ids);

    /**
     * 货品进货汇总
     *
     * @param goods_num
     * @param shop_id
     * @return
     */

    @Select(" select SUM(a.goods_number) as goods_number,b.goods_id,b.goods_name,b.UDI from t_cl_stockInfo a " +
            "left join t_cl_goods b on a.goods_id = b.goods_id where a.stock_status = 0 and a.shop_id = #{shop_id} " +
            "and (b.goods_name like '%${goods_num}%' or b.goods_num like '%${goods_num}%')" +
            " group by b.goods_id,b.goods_name,b.UDI ")
    List<Map<String, Object>> goodsListTotal(Integer shop_id, @Param("goods_num") String goods_num);


    /**
     * 货品进货汇总
     *
     * @param shop_id
     * @param supplier_id
     * @param start_time
     * @param end_time
     * @return
     */
    @Select("<script>" +
            "select sum(a.goods_number) as goods_number ,COUNT(a.goods_id) as goods_num, d.supplier_id, d.supplier_name " +
            "from   t_cl_stockInfo a left join t_cl_goods b on a.goods_id = b.goods_id" +
            " left join t_cl_inStock c on a.stock_num =c.in_num " +
            "left join  t_cl_supplier d on c.supplier_id= d.supplier_id " +
            "left join t_cl_reviewStock e on a.stock_num = e.in_num " +
            "where a.stock_status = 0 and a.shop_id = #{shop_id}" +
            "<if test=' supplier_id!=0'> and c.supplier_id= #{supplier_id} </if> " +
            "<if test=' start_time!=\"0\"'> and step4_date &gt;= #{start_time} </if> " +
            "<if test=' end_time!=\"0\"'> and step4_date &lt;= #{end_time}  </if>" +
            " group by  d.supplier_id, d.supplier_name ,a.goods_id" +
            "</script>")
    List<Map<String, Object>> goodsListGyTotal(Integer shop_id, Integer supplier_id, String start_time, String end_time);


    /**
     * 货品销售汇总
     *
     * @param shop_id
     * @param customer_name
     * @param start_time
     * @param end_time
     * @return
     */
    @Select("<script>" +
            "select goods_id,goods_name,batch_number,production_date,expiration,sum(goods_number) as goods_number,UDI from View_cl_goodsSaleDetail where " +
            "goods_status = 0 and stock_status = 1 and shop_id =#{shop_id} and customer_name like '%${customer_name}%' " +
            "<if test=' start_time!=\"0\"'> and step2_date &gt;= #{start_time} </if> " +
            "<if test=' end_time!=\"0\"'> and step2_date &lt;= #{end_time}  </if>" +
            "group by goods_id,goods_name,batch_number,production_date,expiration,UDI" +
            "</script>")
    List<Map<String, Object>> listGoodsSaleTotal(Integer shop_id, String customer_name, String start_time, String end_time);

}
